﻿package component.validate.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import component.validate.exception.ValidateException;
import component.validate.util.PropertiesUtil;


public class ConnectionFactory {
	private static Object obj = new Object();
	private static ConnectionFactory instance = null;
	
	private ConnectionFactory(){
		
	}
	
	public static ConnectionFactory getInstance() {
		// 双重检查（JVM类载入两次）
		if (instance == null) {
			synchronized (obj) {
				if (instance == null) {
					instance = new ConnectionFactory();
				}
			}
		}
		return instance;
	}
	
	public static IValidateConnection getConnectionUtil(){
		String className = PropertiesUtil.getProperty("connection.impl");
		Class c = null;
		try {
			c = Class.forName(className);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		IValidateConnection connectionUtil = null;
		try {
			connectionUtil = (IValidateConnection)c.newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connectionUtil;
	}
	
	//删除重复的票据
	public static void main(String[] args) throws SQLException, ValidateException {
		IValidateConnection connectionUtil = ConnectionFactory.getConnectionUtil();
		Connection c = connectionUtil.getConnection();
		
		String sql = "select fbill_type_id,fbill_code,count(fbill_code) from TFM_FS_T_BILL_USE1 group by  fbill_type_id,fbill_code having count(fbill_code) >1 fetch first 100 rows only ";
		PreparedStatement pstm = null;
		PreparedStatement pstm2 = null;
		PreparedStatement pstm3 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try{
			pstm = c.prepareStatement(sql);
			rs = pstm.executeQuery();

			while(rs.next()){
				String billTypeId = rs.getString(1);
				String billCode = rs.getString(2);
				int count = rs.getInt(3);
				
				String selectSql = "select fid,fbill_code from TFM_FS_T_BILL_USE1 where fbill_type_id = '" + billTypeId + "' and fbill_code = '" + billCode + "' fetch first 100 rows only ";
				pstm2 = c.prepareStatement(selectSql);
				rs2 = pstm2.executeQuery();
				int i = 1;
				while(rs2.next() && i<count){
					String fid = rs2.getString(1);
					i++;
					
					String delSql = "delete from TFM_FS_T_BILL_USE1 where fid = ?";
					pstm3 = c.prepareStatement( delSql );
					pstm3.setString(1,fid);
					pstm3.execute();
				}
				
			}
	
		}catch(SQLException e){
			e.printStackTrace();
			throw new ValidateException(e);
		}finally{
			if(rs!=null) rs.close();
			if(pstm!=null) pstm.close();
			if(rs2!=null) rs2.close();
			if(pstm2!=null) pstm2.close();
			if(pstm3!=null) pstm3.close();
			connectionUtil.close();
		}
	}

}
